import xlwings as xw
from pathlib import Path
import pandas as pd



def hello_world():
    wb = xw.Book()
    sheet = wb.sheets[0]
    sheet.range('A1').value = 'Hello, world!'
    wb.save('1.xlsx')
    wb.close()

def open_excel():
    wb = xw.Book('1.xlsx')
    sheet = wb.sheets[0]
    print(sheet.range('A1').value)
    wb.close()
    

def open_excel_2():
    app = xw.App(visible=True, add_book=False)
    wb=app.books.open('test.xlsx')
    ws = wb.sheets

    for i in ws:
        table = i.range('a1').expand('table')
        # EXCEL VBA的API接口，Replace是替换单元格内容的函数
        table.api.Replace('切割加工费','加工费')
    wb.save()
    wb.close()
    app.quit()


def open_excel_3():
    '''
    拆分列textToColumns()
    '''
    wb = xw.Book('test.xlsx')
    ws = wb.sheets[0]
    # table = ws.range('a1').expand('table')
    for i in range(3):
        ws.range('M:M').insert(shift='right',copy_origin='format_from_left_or_above')
    size_col = ws.range('l1').expand('down')
    size_col.api.TextToColumns(Destination=ws.range('M1').api,Other=True,OtherChar='*')
    ws.range('M1:O1').value = ['厚度','宽度','长度']
    ws.range('l:l').delete();
    wb.save()
    wb.close()

def open_excel_4():
    '''
    边框加粗
    '''
    wb = xw.Book('test.xlsx')
    ws = wb.sheets[0]
    table = ws.range('a1').expand('table')
    for b in range(7,13):
        table.api.Borders(b).LineStyle = -4119
        table.api.Borders(b).Weight = 4
    wb.save()
    wb.close()

def openFile():
    '''
    批量提取excel文件
    '''

    # 设置工作目录
    file_path = Path('20240726excel\\2024')
    
    file_list = list(file_path.glob('*码头提成.xlsx'))
    for file in file_list:
        if file.name.startswith('~$'):
            continue
        wb = xw.Book(file)
        ws = wb.sheets[1]
        data = ws.range('a5:f12').options(pd.DataFrame, index=False).value
        print(file.name)
        data['合计'].fillna('0',inplace=True)
        data = data.drop_duplicates()
        ws.clear_contents()
        ws.range('a5:f12').options(index=False).value = data
        # print(data)
        wb.save()
        wb.close()
    # for file in file_list:
    #     print(file.name)

def sort_excel():
    '''
    排序excel文件,不用panda
    '''
    wb=xw.Book('test.xlsx')
    ws=wb.sheets[0]
    table=ws.range('a1').expand('table')
    table.api.Sort(Key1=ws.range('a2').api, Order1=1, Header=1, Orientation=1)
    wb.save()
    wb.close()

def sort_excel_2():
    '''
    排序excel文件,用panda
    '''
    wb = xw.Book('test.xlsx')
    ws = wb.sheets[0]
    table = ws.range('a1').expand('table')
    data = table.options(pd.DataFrame, index=False).value
    data = data.sort_values(by='货主',ascending=False)
    ws1 = wb.sheets.add(name='排序后')
    ws1.range('a1').value = data
    wb.save()
    wb.close()

def pivot_table():
    '''
    透视表
    '''
    wb = xw.Book('test.xlsx')
    ws = wb.sheets['sheet']
    table = ws.range('a1').expand('table')
    data = table.options(pd.DataFrame, index=False).value
    pivot_data = pd.pivot_table(data,index=['货主'],values=['结算数量','结算金额'],aggfunc={'结算数量':'sum','结算金额':'sum'})
    ws1 = wb.sheets.add(name='透视表')
    ws1.range('a1').value = pivot_data
    wb.save()
    wb.close()

def transfer_excel():
    '''
    入库单批量制作
    '''
    colNameList = ['品种','产地','钢种','规格','张数','净重','备注']
    File_name=Path('20240726excel\\批量入库单').glob('grid*.xlsx')
    # print(type(File_name))
    for file in File_name:
        if file.name.startswith('~$'):
            continue
        wb = xw.Book(file)
        ws = wb.sheets[0]
        data = ws.range('a1').expand('table').options(pd.DataFrame, index=False).value
        a=data.groupby(by='入库单号')

        for gp_name,gp_data in a:
            print(gp_name)
            # print(gp_data)
            # print(type(gp_data))
            company_name=gp_data.iloc[0]['货主名称']
            wbtemp = xw.Book('20240726excel\\批量入库单\\入库单.xlsx')
            wstemp = wbtemp.sheets['模板'].copy(after=wbtemp.sheets[-1],name=gp_name)
            gp_data=gp_data[colNameList]
            # print(gp_data)
            wstemp.range('b4').options(index=False,header=False).value=gp_data
            wstemp.range('G2').options(index=False,header=False).value=gp_name
        wbtemp.save()
        wbtemp.close()
        wb.save()
        wb.close()



if __name__ == '__main__':
    # hello_world()
    # open_excel()
    # open_excel_2()
    # open_excel_3()
    # open_excel_4()
    # openFile()
    # sort_excel()
    # sort_excel_2()
    # pivot_table()
    transfer_excel()